package cc.messcat.service.fupin;

import cc.messcat.common.model.Pager;
import cc.messcat.common.service.BaseServiceImpl;
import cc.messcat.common.util.StringUtil;
import cc.messcat.dao.area.AreaDao;
import cc.messcat.dao.fupin.PoorVillageDao;
import cc.messcat.entity.area.Area;
import cc.messcat.entity.fupin.PoorVillage;
import cc.messcat.entity.fupin.PoorVillageHistory;
import cc.messcat.entity.system.ApplicationProperties;
import cc.messcat.entity.system.McUser;
import cc.messcat.entity.vo.UnitsNember;
import cc.messcat.util.ExcelUtil;
import cc.messcat.util.Install;
import cc.messcat.util.LevelUtil;
import cc.messcat.util.PoorVillageControllerExcelHandler;
import net.sf.ehcache.CacheManager;
import net.sf.ehcache.Element;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.util.*;

@Service
public class PoorVillageServiceImpl extends BaseServiceImpl<PoorVillage> implements PoorVillageService {

	private static Logger log = LoggerFactory.getLogger(PoorVillageServiceImpl.class);

    @Autowired
    private PoorVillageDao poorViDao;
    @Autowired
    private AreaDao areaDao;
    
    @Autowired
    private CacheManager manager;
    
    @Value("#{ configProperties['web.name'] }")
	private String projectName;
    
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;

    @Override
    public Pager<PoorVillage> getPager(int pageNo, int pageSize) {
        Map map = new HashMap<>();
        String hql = "from PoorVillage where type= :type ORDER BY addTime DESC";
        map.put("type", "0");
        map.put("isDel", "0");
        return this.poorViDao.getPagerByHql(pageNo, pageSize, hql, map);
    }

    @Override
    public Long addPoorVillage(PoorVillage poorVillage) {
        return poorViDao.save1(poorVillage);
    }
    
    /**
     * 获取新的实体类的通用方法
     * @param poorVillage
     * @return
     */
    @Override
    public PoorVillage getPoorVillage(PoorVillage poorVillage){
    	PoorVillage poorVillage0 = new PoorVillage();
    	poorVillage0.setId(poorVillage.getId());
    	poorVillage0.setCty(poorVillage.getCty());
    	poorVillage0.setCry(poorVillage.getCry());
    	poorVillage0.setFpeople(poorVillage.getFpeople());
    	poorVillage0.setPost(poorVillage.getPost());
    	poorVillage0.setContact(poorVillage.getContact());
    	poorVillage0.setNature(poorVillage.getNature());
    	poorVillage0.setDevelopment(poorVillage.getDevelopment());
    	poorVillage0.setTerrain(poorVillage.getTerrain());
    	poorVillage0.setParty(poorVillage.getParty());
    	poorVillage0.setOfficer(poorVillage.getOfficer());
    	poorVillage0.setCuedLand(poorVillage.getCuedLand());
    	poorVillage0.setIrrigation(poorVillage.getIrrigation());
    	poorVillage0.setWoodLand(poorVillage.getWoodLand());
    	poorVillage0.setFarmLand(poorVillage.getFarmLand());
    	poorVillage0.setFruit(poorVillage.getFruit());
    	poorVillage0.setForageGrass(poorVillage.getForageGrass());
    	poorVillage0.setWaters(poorVillage.getWaters());
    	poorVillage0.setTotal(poorVillage.getTotal());
    	poorVillage0.setNatlNumber(poorVillage.getNatlNumber());
    	poorVillage0.setLowHouse(poorVillage.getLowHouse());
    	poorVillage0.setFiveHouse(poorVillage.getFiveHouse());
    	poorVillage0.setTotalNumber(poorVillage.getTotalNumber());
    	poorVillage0.setLowNumber(poorVillage.getLowNumber());
    	poorVillage0.setFiveNumber(poorVillage.getFiveNumber());
    	poorVillage0.setLessNumber(poorVillage.getLessNumber());
    	poorVillage0.setWomanNumber(poorVillage.getWomanNumber());
    	poorVillage0.setRemnantNumber(poorVillage.getRemnantNumber());
    	poorVillage0.setLabourNumber(poorVillage.getLabourNumber());
    	poorVillage0.setGooutNumber(poorVillage.getGooutNumber());
    	poorVillage0.setFarmer(poorVillage.getFarmer());
    	poorVillage0.setCollective(poorVillage.getCollective());
    	poorVillage0.setMedical(poorVillage.getMedical());
    	poorVillage0.setTownPension(poorVillage.getTownPension());
    	poorVillage0.setRuralPension(poorVillage.getRuralPension());
    	poorVillage0.setAsphalt(poorVillage.getAsphalt());
    	poorVillage0.setBus(poorVillage.getBus());
    	poorVillage0.setSecurity(poorVillage.getSecurity());
    	poorVillage0.setDifficulty(poorVillage.getDifficulty());
    	poorVillage0.setLife(poorVillage.getLife());
    	poorVillage0.setElectric(poorVillage.getElectric());
    	poorVillage0.setLifeNumber(poorVillage.getLifeNumber());
    	poorVillage0.setOpenNumber(poorVillage.getOpenNumber());
    	poorVillage0.setDanger(poorVillage.getDanger());
    	poorVillage0.setCooperative(poorVillage.getCooperative());
    	poorVillage0.setMajor(poorVillage.getMajor());
    	poorVillage0.setTourism(poorVillage.getTourism());
    	poorVillage0.setJob(poorVillage.getJob());
    	poorVillage0.setFarm(poorVillage.getFarm());
    	poorVillage0.setFarmIncome(poorVillage.getFarmIncome());
    	poorVillage0.setHygiene(poorVillage.getHygiene());
    	poorVillage0.setToilet(poorVillage.getToilet());
    	poorVillage0.setDoctor(poorVillage.getDoctor());
    	poorVillage0.setGarbage(poorVillage.getGarbage());
    	poorVillage0.setCulture(poorVillage.getCulture());
    	poorVillage0.setBroadband(poorVillage.getBroadband());
    	poorVillage0.setInternet(poorVillage.getInternet());
    	poorVillage0.setVillages(poorVillage.getVillages());
    	poorVillage0.setSchool(poorVillage.getSchool());
    	poorVillage0.setPersonnel(poorVillage.getPersonnel());
    	poorVillage0.setAddTime(poorVillage.getAddTime());
    	poorVillage0.setUpTime(poorVillage.getUpTime());
    	poorVillage0.setNaturals(poorVillage.getNaturals());
    	poorVillage0.setType(poorVillage.getType());
    	poorVillage0.setTuoTime(poorVillage.getTuoTime());
    	poorVillage0.setFanTime(poorVillage.getFanTime());
    	poorVillage0.setIsDel(poorVillage.getIsDel());
    	poorVillage0.setHelpPerunit(poorVillage.getHelpPerunit());
    	poorVillage0.setCode(poorVillage.getCode());
    	poorVillage0.setName(poorVillage.getName());
    	poorVillage0.setGroupNumber(poorVillage.getGroupNumber());
    	poorVillage0.setProvince(poorVillage.getProvince());
    	poorVillage0.setCity(poorVillage.getCity());
    	poorVillage0.setVillage(poorVillage.getVillage());
    	poorVillage0.setPoor(poorVillage.getPoor());
    	poorVillage0.setPoorNumber(poorVillage.getPoorNumber());
    	poorVillage0.setDistance(poorVillage.getDistance());
    	poorVillage0.setRoad(poorVillage.getRoad());
    	poorVillage0.setElectrics(poorVillage.getElectrics());
    	poorVillage0.setLifes(poorVillage.getLifes());
    	poorVillage0.setBroadbands(poorVillage.getBroadbands());
    	poorVillage0.setArea(poorVillage.getArea());
    	poorVillage0.setStatus(poorVillage.getStatus());
    	return poorVillage0;
    }

    @Override
    public void modifyPoorVillage(PoorVillage poorVillage) {
        this.poorViDao.merge(poorVillage);
    }

    @Override
    public void removePoorVillage(Long id) {
        this.poorViDao.deleteById(PoorVillage.class, id);
    }

    @Override
    public PoorVillage get(Long id) {
        return this.poorViDao.get(id);
    }

    @Override
    public List queryPoList() {
        Map map = new HashMap<>();
        String hql = "from PoorVillage where type= :type ORDER BY addTime DESC";
        map.put("type", "0");
        return this.poorViDao.queryList(hql, map);
    }

    @Override
    public List queryPoList1() {
        Map map = new HashMap<>();
        String hql = "from PoorVillage where type= :type ORDER BY addTime DESC";
        map.put("type", "1");
        return this.poorViDao.queryList(hql, map);
    }

    @Override
    public void modifyPoorVillages(PoorVillage poorVillage) {
        poorVillage.setHelpPerunit(null);
        modifyObject(poorVillage);
    }

    @Override
    public List<PoorVillage> poorVillages(Long id) {
        Map map = new HashMap<>();
        String hql = "from PoorVillage where helpPerunit= :HELP_PERUNIT  ORDER BY addTime DESC";
        map.put("HELP_PERUNIT", id);
        return this.poorViDao.queryList(hql, map);
    }

    @Override
    public Pager<PoorVillage> getNaPager(int pageNo, int pageSize) {
        Map map = new HashMap<>();
        String hql = "from PoorVillage where type= :type ORDER BY addTime DESC";
        map.put("type", "1");
        map.put("isDel", "0");
        return this.poorViDao.getPagerByHql(pageNo, pageSize, hql, map);
    }

    /**
     * 自然村模糊查询
     */
    @Override
    public Pager<PoorVillage> getNaPagers(int pageNo, int pageSize, PoorVillage poorVillage, String year, McUser user) {
        Map map = new HashMap<>();
        StringBuffer hql = new StringBuffer();
        hql.append("FROM PoorVillage WHERE 1=1 and isDel= :isDel ");
        //组号
        if (poorVillage.getGroupNumber() != null && !"".equals(poorVillage.getGroupNumber())) {
            String groupNumber = poorVillage.getGroupNumber().replaceAll(" ", "");
            hql.append(" AND groupNumber like '%" + groupNumber + "%'");
        }
        //负责人
        if (poorVillage.getFpeople() != null && !"".equals(poorVillage.getFpeople())) {
            String fpeople = poorVillage.getFpeople().replaceAll(" ", "");
            hql.append(" AND fpeople like '%" + fpeople + "%'");
        }
        //联系电话
        if (poorVillage.getContact() != null && !"".equals(poorVillage.getContact())) {
            String contact = poorVillage.getContact().replaceAll(" ", "");
            hql.append(" AND contact like '%" + contact + "%'");
        }
        //省
        if (poorVillage.getProvince() != null && !"".equals(poorVillage.getProvince())) {
            String province = poorVillage.getProvince().replaceAll(" ", "");
            hql.append(" AND province like '%" + province + "%'");
        }
        //市
        if (poorVillage.getCity() != null && !"".equals(poorVillage.getCity())) {
            String city = poorVillage.getCity().replaceAll(" ", "");
            hql.append(" AND city like '%" + city + "%'");
        }
        //县
        if (poorVillage.getCty() != null && !"".equals(poorVillage.getCty())) {
            String cty = poorVillage.getCty().replaceAll(" ", "");
            hql.append(" AND cty like '%" + cty + "%'");
        }
        //乡
        if(user.getUserLevel().getLevel() == 4){
        	   if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
        		   Area area = areaDao.getAreaByCode(poorVillage.getCry());
                   hql.append(" AND cry like '%" + area.getArea() + "%'");
        	   }else{
        		   Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
               	   String cry = area.getArea(); 
                   hql.append(" AND cry like '%" + cry + "%'");
        	   }            	
        }else{
        	 if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
      		      Area area = areaDao.getAreaByCode(poorVillage.getCry());
                 hql.append(" AND cry like '%" + area.getArea() + "%'");
      	   }
        }
        
        //村        
        if(user.getUserLevel().getLevel() == 5){
        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
        		Area area = areaDao.getAreaByCode(poorVillage.getName());
                hql.append(" AND name like '%" + area.getArea() + "%'");
        	}else{
        		Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
            	String name = area.getArea(); 
            	hql.append(" AND name like '%" + name + "%'");
        	}       	
        }else{
        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
        		Area area = areaDao.getAreaByCode(poorVillage.getName());
                hql.append(" AND name like '%" + area.getArea() + "%'");
        	}
        }
        //村名称
        if (poorVillage.getVillage() != null && !"".equals(poorVillage.getVillage())) {
            String village = poorVillage.getVillage().replaceAll(" ", "");
            hql.append(" AND village like '%" + village + "%'");
        }
        //年度
        if (year != null && !"".equals(year)) {
            String years = year.replaceAll(" ", "");
            hql.append(" AND annual = '" + years + "'");
        }
        map.put("isDel", "0");
       // map.put("type", "1");
        return this.poorViDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
    }

    /**
     * 贫困村模糊查询
     */
    @Override
    public Pager<PoorVillage> getPoPager(int pageNo, int pageSize, PoorVillage poorVillage, String year, McUser user) {
        Map map = new HashMap<>();
        StringBuffer hql = new StringBuffer();
        hql.append("FROM PoorVillage WHERE type= :type and isDel= :isDel ");
        //负责人
        if (poorVillage.getFpeople() != null && !"".equals(poorVillage.getFpeople())) {
            String fpeople = poorVillage.getFpeople().replaceAll(" ", "");
            hql.append(" AND fpeople like '%" + fpeople + "%'");
        }
        //联系电话
        if (poorVillage.getContact() != null && !"".equals(poorVillage.getContact())) {
            String contact = poorVillage.getContact().replaceAll(" ", "");
            hql.append(" AND contact like '%" + contact + "%'");
        }
//        //省
//        if (poorVillage.getProvince() != null && !"".equals(poorVillage.getProvince())) {
//            String province = poorVillage.getProvince().replaceAll(" ", "");
//            hql.append(" AND province like '%" + province + "%'");
//        }
//        //市
//        if (poorVillage.getCity() != null && !"".equals(poorVillage.getCity())) {
//            String city = poorVillage.getCity().replaceAll(" ", "");
//            hql.append(" AND city like '%" + city + "%'");
//        }
//        //县
//        if (poorVillage.getCty() != null && !"".equals(poorVillage.getCty())) {
//            String cty = poorVillage.getCty().replaceAll(" ", "");
//            hql.append(" AND cty like '%" + cty + "%'");
//        }
        //未审核不查询等级
        if(!"未审核".equals(user.getCheckState())){
        	//乡
        	if(user.getUserLevel().getLevel() == 4){
        		if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
        			Area area = areaDao.getAreaByCode(poorVillage.getCry());
        			hql.append(" AND cry like '%" + area.getArea() + "%'");
        		}else{
        			Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
        			String cry = area.getArea(); 
        			hql.append(" AND cry like '%" + cry + "%'");
        		}            	
        	}else{
        		if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
        			Area area = areaDao.getAreaByCode(poorVillage.getCry());
        			hql.append(" AND cry like '%" + area.getArea() + "%'");
        		}
        	}
        
        
	        //村        
	        if(user.getUserLevel().getLevel() == 5){
	        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
	        		Area area = areaDao.getAreaByCode(poorVillage.getName());
	                hql.append(" AND name like '%" + area.getArea() + "%'");
	        	}else{
	        		Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
	            	String name = area.getArea(); 
	            	hql.append(" AND name like '%" + name + "%'");
	        	}       	
	        }else{
	        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
	        		Area area = areaDao.getAreaByCode(poorVillage.getName());
	                hql.append(" AND name like '%" + area.getArea() + "%'");
	        	}
	        }
        }
        //年度
        if (year != null && !"".equals(year)) {
            String years = year.replaceAll(" ", "");
            hql.append(" AND annual = '" + years + "'");
        }
        //村属性
        if (poorVillage.getNature() != null && !"".equals(poorVillage.getNature()) && !poorVillage.getNature().equals("非贫困户") ) {
            String nature = poorVillage.getNature().replaceAll(" ", "");
            hql.append(" AND nature= '" + nature + "' ");
        }
        //发展方向
        if (poorVillage.getDevelopment() != null && !"".equals(poorVillage.getDevelopment())) {
            String development = poorVillage.getDevelopment().replaceAll(" ", "");
            hql.append(" AND development like '%" + development + "%'");
        }
        hql.append(" order by id asc");
        map.put("type", "0");
        map.put("isDel", "0");
        return this.poorViDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
    }
    
    /**
     * 未出列贫困村
     * @param pageNo
     * @param pageSize
     * @param poorVillage
     * @param year
     * @return
     */
    @Override
    public Pager<PoorVillage> getPoPager2(int pageNo, int pageSize, PoorVillage poorVillage, String year, McUser user) {
        Map map = new HashMap<>();
        StringBuffer hql = new StringBuffer();
        hql.append("FROM PoorVillage WHERE 1=1  and type= :type and isDel= :isDel and status= :status ");
        //负责人
        if (poorVillage.getFpeople() != null && !"".equals(poorVillage.getFpeople())) {
            String fpeople = poorVillage.getFpeople().replaceAll(" ", "");
            hql.append(" AND fpeople like '%" + fpeople + "%'");
        }
        //联系电话
        if (poorVillage.getContact() != null && !"".equals(poorVillage.getContact())) {
            String contact = poorVillage.getContact().replaceAll(" ", "");
            hql.append(" AND contact like '%" + contact + "%'");
        }
        //省
        if (poorVillage.getProvince() != null && !"".equals(poorVillage.getProvince())) {
            String province = poorVillage.getProvince().replaceAll(" ", "");
            hql.append(" AND province like '%" + province + "%'");
        }
        //市
        if (poorVillage.getCity() != null && !"".equals(poorVillage.getCity())) {
            String city = poorVillage.getCity().replaceAll(" ", "");
            hql.append(" AND city like '%" + city + "%'");
        }
        //县
        if (poorVillage.getCty() != null && !"".equals(poorVillage.getCty())) {
            String cty = poorVillage.getCty().replaceAll(" ", "");
            hql.append(" AND cty like '%" + cty + "%'");
        }
        //乡
        if(user.getUserLevel().getLevel() == 4){
        	   if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
        		   Area area = areaDao.getAreaByCode(poorVillage.getCry());
                   hql.append(" AND cry like '%" + area.getArea() + "%'");
        	   }else{
        		   Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
               	   String cry = area.getArea(); 
                   hql.append(" AND cry like '%" + cry + "%'");
        	   }            	
        }else{
        	 if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
      		      Area area = areaDao.getAreaByCode(poorVillage.getCry());
                 hql.append(" AND cry like '%" + area.getArea() + "%'");
      	   }
        }
        
        //村        
        if(user.getUserLevel().getLevel() == 5){
        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
        		Area area = areaDao.getAreaByCode(poorVillage.getName());
                hql.append(" AND name like '%" + area.getArea() + "%'");
        	}else{
        		Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
            	String name = area.getArea(); 
            	hql.append(" AND name like '%" + name + "%'");
        	}       	
        }else{
        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
        		Area area = areaDao.getAreaByCode(poorVillage.getName());
                hql.append(" AND name like '%" + area.getArea() + "%'");
        	}
        }
        //年度
        if (year != null && !"".equals(year)) {
            String years = year.replaceAll(" ", "");
            hql.append(" AND annual = '" + years + "'");
        }
        //村属性
        if (poorVillage.getNature() != null && !"".equals(poorVillage.getNature())) {
            String nature = poorVillage.getNature().replaceAll(" ", "");
            hql.append(" AND nature= '" + nature + "' ");
        }
        //发展方向
        if (poorVillage.getDevelopment() != null && !"".equals(poorVillage.getDevelopment())) {
            String development = poorVillage.getDevelopment().replaceAll(" ", "");
            hql.append(" AND development like '%" + development + "%'");
        }
        map.put("type", "0");
        map.put("status", "0");
        map.put("isDel", "0");
        return this.poorViDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
    }
    
    /**
     * 已出列贫困村
     * @param pageNo
     * @param pageSize
     * @param poorVillage
     * @param year
     * @return
     */
    @Override
    public Pager<PoorVillage> getPoPager3(int pageNo, int pageSize, PoorVillage poorVillage, String year, McUser user) {
        Map map = new HashMap<>();
        StringBuffer hql = new StringBuffer();
        hql.append("FROM PoorVillage WHERE 1=1  and type= :type and isDel= :isDel and status= :status ");
        //负责人
        if (poorVillage.getFpeople() != null && !"".equals(poorVillage.getFpeople())) {
            String fpeople = poorVillage.getFpeople().replaceAll(" ", "");
            hql.append(" AND fpeople like '%" + fpeople + "%'");
        }
        //联系电话
        if (poorVillage.getContact() != null && !"".equals(poorVillage.getContact())) {
            String contact = poorVillage.getContact().replaceAll(" ", "");
            hql.append(" AND contact like '%" + contact + "%'");
        }
        //省
        if (poorVillage.getProvince() != null && !"".equals(poorVillage.getProvince())) {
            String province = poorVillage.getProvince().replaceAll(" ", "");
            hql.append(" AND province like '%" + province + "%'");
        }
        //市
        if (poorVillage.getCity() != null && !"".equals(poorVillage.getCity())) {
            String city = poorVillage.getCity().replaceAll(" ", "");
            hql.append(" AND city like '%" + city + "%'");
        }
        //县
        if (poorVillage.getCty() != null && !"".equals(poorVillage.getCty())) {
            String cty = poorVillage.getCty().replaceAll(" ", "");
            hql.append(" AND cty like '%" + cty + "%'");
        }
        //乡
        if(user.getUserLevel().getLevel() == 4){
        	   if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
        		   Area area = areaDao.getAreaByCode(poorVillage.getCry());
                   hql.append(" AND cry like '%" + area.getArea() + "%'");
        	   }else{
        		   Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
               	   String cry = area.getArea(); 
                   hql.append(" AND cry like '%" + cry + "%'");
        	   }            	
        }else{
        	 if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
      		      Area area = areaDao.getAreaByCode(poorVillage.getCry());
                 hql.append(" AND cry like '%" + area.getArea() + "%'");
      	   }
        }
        
        //村        
        if(user.getUserLevel().getLevel() == 5){
        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
        		Area area = areaDao.getAreaByCode(poorVillage.getName());
                hql.append(" AND name like '%" + area.getArea() + "%'");
        	}else{
        		Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
            	String name = area.getArea(); 
            	hql.append(" AND name like '%" + name + "%'");
        	}       	
        }else{
        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
        		Area area = areaDao.getAreaByCode(poorVillage.getName());
                hql.append(" AND name like '%" + area.getArea() + "%'");
        	}
        }
        //年度
        if (year != null && !"".equals(year)) {
            String years = year.replaceAll(" ", "");
            hql.append(" AND annual = '" + years + "'");
        }
        //村属性
        if (poorVillage.getNature() != null && !"".equals(poorVillage.getNature())) {
            String nature = poorVillage.getNature().replaceAll(" ", "");
            hql.append(" AND nature= '" + nature + "' ");
        }
        //发展方向
        if (poorVillage.getDevelopment() != null && !"".equals(poorVillage.getDevelopment())) {
            String development = poorVillage.getDevelopment().replaceAll(" ", "");
            hql.append(" AND development like '%" + development + "%'");
        }
        map.put("type", "0");
        map.put("status", "1");
        map.put("isDel", "0");
        return this.poorViDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
    }

    @Override
    public Pager<PoorVillage> getVillagesByCondition(int pageNo, int pageSize, PoorVillage poorVillage,McUser user) {
        Map map = new HashMap<>();
        StringBuffer hql = new StringBuffer();
        hql.append("FROM PoorVillage WHERE 1=1 and isDel= :isDel and annual= :annual");
        //省
        if (poorVillage.getProvince() != null && !"".equals(poorVillage.getProvince())) {
            String province = poorVillage.getProvince().replaceAll(" ", "");
            hql.append(" AND province like '%" + province + "%'");
        }
        //市
        if (poorVillage.getCity() != null && !"".equals(poorVillage.getCity())) {
            String city = poorVillage.getCity().replaceAll(" ", "");
            hql.append(" AND city like '%" + city + "%'");
        }
        //县
        if (poorVillage.getCty() != null && !"".equals(poorVillage.getCty())) {
            String cty = poorVillage.getCty().replaceAll(" ", "");
            hql.append(" AND cty like '%" + cty + "%'");
        }
        //乡
        if(user.getUserLevel().getLevel() == 4){
        	   if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
//        		   Area area = areaDao.getAreaByCode(poorVillage.getCry());
//                   hql.append(" AND cry like '%" + area.getArea() + "%'");
        		 String cry = poorVillage.getCry();
          		 if(cry.matches("\\w+")) {
          			 Area area = areaDao.getAreaByCode(cry);
          			 hql.append(" AND cry like '%" + (area != null ? area.getArea(): "none") + "%'");
          		 } else {
          			 hql.append(" AND cry like '%" + cry + "%'");
          		 }
        	   }else{
        		   Area area = areaDao.get(user.getArea().getId());
               	   String cry = area.getArea(); 
                   hql.append(" AND cry like '%" + cry + "%'");
        	   }            	
        }else{
        	 if(poorVillage.getCry() != null && !"".equals(poorVillage.getCry())){
//      		     Area area = areaDao.getAreaByCode(poorVillage.getCry());
//                 hql.append(" AND cry like '%" + area.getArea() + "%'");
        		 
        		 String cry = poorVillage.getCry();
        		 if(cry.matches("\\w+")) {
        			 Area area = areaDao.getAreaByCode(cry);
        			 hql.append(" AND cry like '%" + (area != null ? area.getArea(): "none") + "%'");
        		 } else {
        			 hql.append(" AND cry like '%" + cry + "%'");
        		 }
      	   }
        }
        
        //村        
        if(user.getUserLevel().getLevel() == 5){
        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
//        		Area area = areaDao.getAreaByCode(poorVillage.getName());
//                hql.append(" AND name like '%" + area.getArea() + "%'");
        		 String village = poorVillage.getName();
        		 if(village.matches("\\w+")) {
        			 Area area = areaDao.getAreaByCode(village);
        			 hql.append(" AND cry like '%" + (area != null ? area.getArea(): "none") + "%'");
        		 } else {
        			 hql.append(" AND cry like '%" + village + "%'");
        		 }
        	}else{
        		Area area = areaDao.get(user.getArea().getId());
            	String name = area.getArea(); 
            	hql.append(" AND name like '%" + name + "%'");
        	}       	
        }else{
        	if(poorVillage.getName() != null && !"".equals(poorVillage.getName())){
//        		Area area = areaDao.getAreaByCode(poorVillage.getName());
//                hql.append(" AND name like '%" + area.getArea() + "%'");
       		 String village = poorVillage.getName();
    		 if(village.matches("\\w+")) {
    			 Area area = areaDao.getAreaByCode(village);
    			 hql.append(" AND name like '%" + (area != null ? area.getArea(): "none") + "%'");
    		 } else {
    			 hql.append(" AND name like '%" + village + "%'");
    		 }
        	}
        }
        //村
//        if (poorVillage.getHelpPerunit() != null && !"".equals(poorVillage.getHelpPerunit())) {
//            Long helpUnitId = poorVillage.getHelpPerunit();
        hql.append(" AND (helpPerunit is null OR helpPerunit ='') ");
//        }
        map.put("isDel", "0");
        map.put("annual", ApplicationProperties.getAnnual());
        return this.poorViDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
    }

    @Override
    public List<PoorVillage> getVillagesByCondition(PoorVillage poorVillage) {
        List<PoorVillage> result = poorViDao.getVillagesByCondition(poorVillage);
        return result;

    }

    @Override
    public PoorVillage getVillageByAreaId(Long areaId) {
        return this.poorViDao.getVillageByAreaId(areaId);

    }

    @Override
    public Date getAddTimeById(Long id) {

        return this.poorViDao.getAddTimeById(id);
    }

    @Override
    public PoorVillage getVillageByAreaIdWithClear(Long id,String annual) {
        PoorVillage result = this.poorViDao.getVillageByAreaIdWithClear(id,annual);
        return result;
    }

    @Override
	public boolean isExsitVillage(Long id,String annual) {
        return this.poorViDao.isExsitVillage(id,annual);
    }

    /**
	 * 贫困村个数
	 */
	@Override
	public List unitsNr( ) {
//		Element element = manager.getCache("indexStatistics").get("NemberPoorVillages");
//		if(element != null) {
//			return (List) element.getObjectValue();
//		}
		
			String hql = "select area FROM Area where fatherId = '3' ";
			List<String> country = poorViDao.find(hql);
			List<UnitsNember> uNember = new ArrayList<UnitsNember>();
			for (String cy : country) {
				String hql_2 = "FROM PoorVillage where cry = '"+cy+"' and type='0' and isDel=0 and annual = "+ApplicationProperties.getAnnual()+"";
				uNember.add(new UnitsNember(cy,poorViDao.find(hql_2).size()));
			}
			
			manager.getCache("indexStatistics").put(new Element("NemberPoorVillages", uNember));
			return uNember;
		}

	/**
	 * 贫困村识别标准统计
	 */
	@Override
	@SuppressWarnings("all")
	public Pager poorVillageStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		status = "未出列".equals(status) ? "0" : "已出列".equals(status) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVillageStatistics(pageNo,  pageSize,  year,  country,  village, status);
		
		return page;
	}

	/**
	 * 贫困村识别标准统计excel导出
	 */
	@Override
	@SuppressWarnings("all")
	public HSSFWorkbook exportPoorVillageStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String type) {
		
		String[] cellName = new String[] { "序号", "地区", "总贫困村数", "贫困村数", "贫困村占比", "经济薄弱村数", "经济薄弱村占比", "十二五贫困村数",
			"十二五贫困村占比", "十三五贫困村数", "十三五贫困村占比" };
		String[] field = new String[] { "area", "totalPoorVillage", "pkCount", "pkPercentage", "jjbrCount", "jjbrPercentage",
			"sewCount", "sewPercentage", "sswCount", "sswPercentage" };
		
		List list  = new ArrayList<>();
		Map map = null;
		for(Map e : (List<Map>)poorVillageStatistics(user, 1, 5000, year,country , village, type).getResultList()) {
			map = new HashMap<>((Map)e.get("verbose"));
			map.put("area", e.get("area"));
			map.put("totalPoorVillage", e.get("totalPoorVillage").toString());
			list.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村识别标准统计表");
		sheet.setDefaultColumnWidth(18);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村识别标准统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		
		return workbook;
	}


	/**
	 * 贫困村导入
	 */
	@SuppressWarnings("unchecked")
	@Override
	public String adduploadFile(String file, HttpSession session, McUser mcUser) throws Exception {
		// 获取本地exc全路径
		String classPath = this.getClass().getClassLoader().getResource("").getPath();
		String excelFile = classPath.substring(1, classPath.indexOf("/WEB-INF")) + file;
		File files = new File(excelFile);
		if (!files.exists()) {
			return "NO_URL";
		}
		// 解析Excel文件
		Map data = new HashMap();
		try {
			// 查询所有 行政区划编码
			List<String> areaList = areaDao.retrieveAllareaCode();
			data = PoorVillageControllerExcelHandler.readExcel(excelFile, areaList);
			if (StringUtil.isNotBlank((String) data.get("errInfo"))) {
				return data.get("errInfo").toString();
			}
			data.remove("errInfo");
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//最后删除上传文件
			files.delete();
		}
		// 获取数据比较
		long dataTimeS = System.currentTimeMillis();
		// 地区数据：
		Map<String, Area> areaMap = gainAreaMap();
		// 贫困村数据：
		Map<String, Long> poorvMap = gainPoorvMap();
		//储存已插入的贫困村
		Map<String,Long> poorvMaps = new HashMap<String,Long>();
		
		long dataTimeE = System.currentTimeMillis();
		log.info(dataTimeE - dataTimeS + "S>>>>>>>>>>>>>>>贫困村导入 获取数据耗时");
		
		//储存需要添加的贫困村
		List<Map<String, Object>> addPoorVillageList= new ArrayList<Map<String, Object>>();
		//储存需要更新的贫困村
		List<Map<String, Object>> upPoorVillageList= new ArrayList<Map<String, Object>>();

		// 依次将data中的数据映射到Signind对象并保存到数据库
		Set<Object> keySet = data.keySet();
		for (Object obj : keySet) {
			ArrayList<PoorVillage> poorVillageList = new ArrayList<PoorVillage>();
			poorVillageList = (ArrayList<PoorVillage>) data.get(obj);
			for (PoorVillage poorvExc : poorVillageList) {
				try {
					//储存添加或者更新贫困村
					addOrUpdatePoorVillage(areaMap, poorvMap,poorvMaps,poorvExc,addPoorVillageList,upPoorVillageList);
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
		}
		
		String sql= null;
		// 添加 处理
		if (addPoorVillageList != null && addPoorVillageList.size() > 0) {
		     sql = "insert into poor_village(TYPE,IS_DEL,STATUS,AREA_ID,ANNUAL,CODE,Cty,Cry,Name,Village,Fpeople,Contact,Nature,Total,TOTAL_NUMBER,Natl_Number,Poor,Poor_Number,Low_Number,Low_House,Five_Number,Five_House,Less_Number,Woman_Number,Remnant_Number,Labour_Number,Goout_Number,Province,City,ADD_TIME) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			Install.executeBatch(sql, 30, addPoorVillageList);
		}
		// 更新 处理
		if (upPoorVillageList != null && upPoorVillageList.size() > 0) {
			sql = "update poor_village set TYPE=?,IS_DEL=?,STATUS=?,AREA_ID=?,ANNUAL=?,CODE=?,Cty=?,Cry=?,Name=?,Village=?,Fpeople=?,Contact=?,Nature=?,Total=?,TOTAL_NUMBER=?,Natl_Number=?,Poor=?,Poor_Number=?,Low_Number=?,Low_House=?,Five_Number=?,Five_House=?,Less_Number=?,Woman_Number=?,Remnant_Number=?,Labour_Number=?,Goout_Number=?,Province=?,City=?,UP_TIME=? where id=? ";
			Install.executeBatch(sql, 31, upPoorVillageList);
		}

		//清除数据
		areaMap.clear();
		poorvMap.clear();
		poorvMaps.clear();
		addPoorVillageList.clear();
		upPoorVillageList.clear();
		return "T";

	}

	 

	/**
	 * 储存添加或者更新贫困村
	 * 
	 * @param areaMap
	 * @param poorvMap
	 * @param poorvMaps 
	 * @param poorvExc
	 * @param isBackup 
	 * @param upPoorVillage 
	 * @param addPoorVillage 
	 */
	public void addOrUpdatePoorVillage(Map<String, Area> areaMap, Map<String, Long> poorvMap, Map<String, Long> poorvMaps, PoorVillage poorvExc, List<Map<String, Object>> addPoorVillageList, List<Map<String, Object>> upPoorVillageList ) {
		// 贫困村ID
		   Long pId = null;
			PoorVillage poorv = new  PoorVillage();
			// 根据行政区划编码判断系统内当前业务年度下是否存在对应的贫困村信息(更新两种情况)
			// 一.在新插入的poorvMaps贫困村里面找是否有该数据
			if (poorvMaps.containsKey(poorvExc.getCode())) {
				pId = poorvMaps.get(poorvExc.getCode());
				poorv.setId(pId);
			}
			// 二.在数据库查找poorvMap贫困村是否有该数据
			else if (poorvMap.containsKey(poorvExc.getCode())) {
				pId = poorvMap.get(poorvExc.getCode());
				poorv.setId(pId);
			}
			// type;默认0:贫困村 (否) 1：转为自然村(是)
			poorv.setType("非贫困村".equals(poorvExc.getNature()) ? "1" : "0");
			poorv.setIsDel("0");// 删除 默认0 1为删除
			poorv.setStatus("否".equals(poorvExc.getType()) ? "0" : "是".equals(poorvExc.getType()) ? "1" : "");// 状态：默认0为新增贫困村
			//根据行政编号判断地区
			poorv.setArea(areaMap.get(poorvExc.getCode()));
			int year = Calendar.getInstance().get(Calendar.YEAR);
			poorv.setAnnual(ApplicationProperties.getAnnual());// 业务年度
			poorv.setCode(poorvExc.getCode());//行政区划编码
			poorv.setCty(poorvExc.getCty());//县
			poorv.setCry(poorvExc.getCry());//乡镇
			poorv.setName(poorvExc.getName());//村
			poorv.setVillage(poorvExc.getName());
			poorv.setFpeople(poorvExc.getFpeople());//负责人
			poorv.setContact(poorvExc.getContact());//村办公电话
			poorv.setNature(poorvExc.getNature());//村属性
			poorv.setTotal(poorvExc.getTotal());//总户数
			poorv.setTotalNumber(poorvExc.getTotalNumber());//总人口数
			poorv.setNatlNumber(poorvExc.getNatlNumber());//自然村数
			poorv.setPoor(poorvExc.getPoor());//贫困户数
			poorv.setPoorNumber(poorvExc.getPoorNumber());//贫困人口数
			poorv.setLowNumber(poorvExc.getLowNumber());//低保人口数
			poorv.setLowHouse(poorvExc.getLowHouse());//低保户数
			poorv.setFiveNumber(poorvExc.getFiveNumber());//五保人口数
			poorv.setFiveHouse(poorvExc.getFiveHouse());//五保户数
			poorv.setLessNumber(poorvExc.getLessNumber());//少数民族人口数
			poorv.setWomanNumber(poorvExc.getWomanNumber());//妇女人口数
			poorv.setRemnantNumber(poorvExc.getRemnantNumber());//残疾人口数
			poorv.setLabourNumber(poorvExc.getLabourNumber());//劳动力人数
			poorv.setGooutNumber(poorvExc.getGooutNumber());//外出务工人数
			poorv.setProvince(poorvExc.getProvince());//省
			poorv.setCity(poorvExc.getCity());//市（州）
		if (pId != null && !poorvMaps.containsKey(poorvExc.getCode())) {
			// poorv.setModifier();//修改人 表中没有该字段
			poorv.setUpTime(new Date());
			// this.poorViDao.update(poorv);
			// 储存已更新或添加的贫困村信息
			poorvMaps.put(poorv.getCode(), poorv.getId());
			///////////////////STA（获取Batch数据）
			// 储存需要更新的Map
			Map<String, Object> upPoorVillageMap = PoorVillageBatchPara(poorv, 1);
			upPoorVillageList.add(upPoorVillageMap);
			///////////////////END
		} else if (pId == null) {
			// poorv.setOne_man();//制单人 表中没有该字段
			poorv.setAddTime(new Date());// 制单时间
			// this.poorViDao.save(poorv);
			// 储存已更新或添加的贫困村信息
			poorvMaps.put(poorv.getCode(), poorv.getId());
			///////////////////STA（获取Batch数据）
			// 储存需要添加的Map
			Map<String, Object> addPoorVillageMap = PoorVillageBatchPara(poorv, 0);
			addPoorVillageList.add(addPoorVillageMap);
			/////////////////// END
		}
	}

	/**
	 * 获取Batch数据
	 * 
	 * @param poorv
	 * @param type
	 *            0添加 1更新 2删除
	 * @return
	 */
	private Map<String, Object> PoorVillageBatchPara(PoorVillage poorv, Integer type) {
		Map<String, Object> addPoorVillageMap = new HashMap<String, Object>();
		addPoorVillageMap.put("1", poorv.getType());
		addPoorVillageMap.put("2", poorv.getIsDel());
		addPoorVillageMap.put("3", poorv.getStatus());
		addPoorVillageMap.put("4", poorv.getArea()!=null?poorv.getArea().getId():null);
		addPoorVillageMap.put("5", poorv.getAnnual());
		addPoorVillageMap.put("6", poorv.getCode());
		addPoorVillageMap.put("7", poorv.getCty());
		addPoorVillageMap.put("8", poorv.getCry());
		addPoorVillageMap.put("9", poorv.getName());
		addPoorVillageMap.put("10", poorv.getVillage());
		addPoorVillageMap.put("11", poorv.getFpeople());
		addPoorVillageMap.put("12", poorv.getContact());
		addPoorVillageMap.put("13", poorv.getNature());
		addPoorVillageMap.put("14", poorv.getTotal());
		addPoorVillageMap.put("15", poorv.getTotalNumber());
		addPoorVillageMap.put("16", poorv.getNatlNumber());
		addPoorVillageMap.put("17", poorv.getPoor());
		addPoorVillageMap.put("18", poorv.getPoorNumber());
		addPoorVillageMap.put("19", poorv.getLowNumber());
		addPoorVillageMap.put("20", poorv.getLowHouse());
		addPoorVillageMap.put("21", poorv.getFiveNumber());
		addPoorVillageMap.put("22", poorv.getFiveHouse());
		addPoorVillageMap.put("23", poorv.getLessNumber());
		addPoorVillageMap.put("24", poorv.getWomanNumber());
		addPoorVillageMap.put("25", poorv.getRemnantNumber());
		addPoorVillageMap.put("26", poorv.getLabourNumber());
		addPoorVillageMap.put("27", poorv.getGooutNumber());
		addPoorVillageMap.put("28", poorv.getProvince());
		addPoorVillageMap.put("29", poorv.getCity());
		if(type == 0){
			addPoorVillageMap.put("30", poorv.getAddTime());
		}
		if(type==1){
			addPoorVillageMap.put("30", poorv.getUpTime());
			addPoorVillageMap.put("31", poorv.getId());//id
		}
		return addPoorVillageMap;
	}

	/**
	 * 获取所有贫困村数据遍历到Map
	 * @param b 
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Long> gainPoorvMap() {
	/*	Map<String, PoorVillage> poorvMap = new HashMap<String, PoorVillage>();
		Map map = new HashMap<>();
		map.put("annual", new SimpleDateFormat("YYYY").format(new Date()));
		
		List<PoorVillage> PoorVillages = this.poorViDao
				.queryList("from PoorVillage where isDel='0' and  annual=:annual ", map);
		for (PoorVillage p : PoorVillages) {
			poorvMap.put(p.getCode(), p);
		}
		return poorvMap;*/
		
		Map<String, Long> poorvMap = new HashMap<String, Long>();
		Map map = new HashMap<>();
		map.put("annual", ApplicationProperties.getAnnual());
		List<Object[]> poorVillagesList = this.poorViDao
				.queryList("select code ,id from PoorVillage where isDel='0' and  annual=:annual ", map);
		
		for (Object[] p : poorVillagesList) {
			poorvMap.put((String) p[0], (Long) p[1]);
		}
		return poorvMap;
		
	}

	/**
	 * 获取所有地区数据遍历到Map
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Area> gainAreaMap() {
		Map<String, Area> areaMap = new HashMap<String, Area>(1024);
		List<Area> areas = this.poorViDao.queryList(" from Area a ", new HashMap<>(0));
		for (Area a : areas) {
			areaMap.put(a.getAreaCode(), a);
		}
		return areaMap;
	}
	
	/**
	 * 自然村管理导出
	 */
	@Override
	public HSSFWorkbook exportNaVillage(String year, PoorVillage poorVillage, McUser user,
			HttpServletRequest req) {
			String[] cellName = new String[]
					{"序号",
							"悬","乡","自然村编号","行政村名称","自然村名称",
							"组号","负责人","联系方式","总户数",
							"贫困户数","低保户数","五保户数","总人口数",
							"贫困人口数","低保人口数","五保人口数",	"少数民族人口数",
							"妇女人口数","残疾人口数","劳保力数","外出务工人数	",
							"村属性"};
			String[] field = new String[]
						{"cty","cry","code","name","village",
							"groupNumber","fpeople","contact","total",
							"poor","lowHouse","fiveHouse","totalNumber",
							"poorNumber","lowNumber","fiveNumber","lessNumber",
							"womanNumber","remnantNumber","labourNumber","gooutNumber",
							"nature"};
			
			Integer sheetCount = 0;
			Pager pager = null;
			Map<Integer,List> excelResult = new HashMap<>();
			
			do {
				sheetCount++;
				pager  = getNaPagers(sheetCount,50000,poorVillage,year,user);
				excelResult.put(sheetCount, pager.getResultList());
				
			} while(sheetCount < (pager.getRowCount()+49999) / 50000);
			
			HSSFWorkbook workbook = new HSSFWorkbook();
			
			for(int i = 1; i <= excelResult.size(); i++) {
				HSSFSheet sheet = workbook.createSheet("自然村记录表" + i);
				sheet.setDefaultColumnWidth(13);
				
				HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
				HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
				HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

				ExcelUtil.addTitle(sheet,cellName,"自然村记录表",headStyle,columnTitleStyle);
				ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
			}
			return workbook;
		}

	/**
	 * 贫困村管理导出
	 */
	@Override
	public HSSFWorkbook exportPoVillage(String year, PoorVillage poorVillage, McUser user,
			HttpServletRequest req) {
			String[] cellName = new String[]
					{"序号","县","乡","村名称","组号","负责人",
							"联系方式","总户数","贫困户数","低保户数","五保户数",
							"总人口数","贫困人口数","低保人口数","五保人口数","少数民族人口数",
							"妇女人口数","残疾人口数","劳保力数","外出务工人数","村属性"};
			String[] field = new String[]
					{"cty","cry","name","groupNumber","fpeople",
							"contact","total","poor","lowHouse","fiveHouse",
							"totalNumber","poorNumber","lowNumber","fiveNumber","lessNumber",
							"womanNumber","remnantNumber","labourNumber","gooutNumber","nature"};
			
			Integer sheetCount = 0;
			Pager pager = null;
			Map<Integer,List> excelResult = new HashMap<>();
			
			do {
				sheetCount++;
				pager  = getPoPager(sheetCount,50000,poorVillage,year,user);
				excelResult.put(sheetCount, pager.getResultList());
					
			} while(sheetCount < (pager.getRowCount()+49999) / 50000);
			
			HSSFWorkbook workbook = new HSSFWorkbook();
			
			for(int i = 1; i <= excelResult.size(); i++) {
				HSSFSheet sheet = workbook.createSheet("贫困村记录表" + i);
				sheet.setDefaultColumnWidth(13);
				
				HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
				HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
				HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

				ExcelUtil.addTitle(sheet,cellName,"贫困村记录表",headStyle,columnTitleStyle);
				ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
			}
			return workbook;
		}

	
	/**
	 * 自然村查询导出
	 */
	@Override
	public HSSFWorkbook exportNaVillageZ(String year, PoorVillage poorVillage, McUser user,
			HttpServletRequest req) {
			String[] cellName = new String[]
					{"序号",
							"自然村名称","自然村组号","自然村人数","村负责人",
							"村办公电话","村属性","出列标志","总户数",
							"总人口数","自然村数","贫困人口数"};
			String[] field = new String[]
						{"name","groupNumber","natlNumber","fpeople",
							"contact","nature","type","total",
							"totalNumber","natlNumber","poorNumber"};
			
			Integer sheetCount = 0;
			Pager pager = null;
			Map<Integer,List> excelResult = new HashMap<>();
			
			do {
				sheetCount++;
				pager  = getNaPagers(sheetCount,50000,poorVillage,year,user);
				excelResult.put(sheetCount, pager.getResultList());
				
			} while(sheetCount < (pager.getRowCount()+49999) / 50000);
			
			HSSFWorkbook workbook = new HSSFWorkbook();
			
		for(Object e :pager.getResultList()) {
			if (StringUtil.isNotBlank(((PoorVillage)e).getType())) {
				if (((PoorVillage)e).getType().equals("0")) {
					((PoorVillage)e).setType("未出列");
				}
				if (((PoorVillage)e).getType().equals("1")) {
					((PoorVillage)e).setType("出列");
				}
			}
		}
			
			for(int i = 1; i <= excelResult.size(); i++) {
				HSSFSheet sheet = workbook.createSheet("自然村记录表" + i);
				sheet.setDefaultColumnWidth(13);
				
				HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
				HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
				HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

				ExcelUtil.addTitle(sheet,cellName,"自然村记录表",headStyle,columnTitleStyle);
				ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
			}
			return workbook;
		}

	/**
	 * 贫困村查询导出
	 */
	@Override
	public HSSFWorkbook exportPoVillageZ(String year, PoorVillage poorVillage, McUser user,
			HttpServletRequest req) {
			String[] cellName = new String[]
					{"序号",
							"行政区划代码","省","市","县","乡（镇）",
							"村","村负责人","村办公电话","村属性","出列标志",
							"总户数","总人口数","自然村数","贫困人口数","贫困户数",
							"低保人口数","低保户数","五保人口数","五保户数","少数民族人口数",
							"妇女人口数","残疾人口数","劳动力人数"};
			String[] field = new String[]
					{"code","province","city","cty","cry",
							"village","fpeople","contact","nature","status",
							"total","totalNumber","natlNumber","poorNumber","poor",
							"lowNumber","lowHouse","fiveNumber","fiveHouse","lessNumber",
							"womanNumber","remnantNumber","labourNumber"};
			
			Integer sheetCount = 0;
			Pager pager = null;
			Map<Integer,List> excelResult = new HashMap<>();
			
			do {
				sheetCount++;
				pager  = getPoPager(sheetCount,50000,poorVillage,year,user);
				excelResult.put(sheetCount, pager.getResultList());
				
			} while(sheetCount < (pager.getRowCount()+49999) / 50000);
			
			HSSFWorkbook workbook = new HSSFWorkbook();
			
		for(Object e :pager.getResultList()) {
			if (StringUtil.isNotBlank(((PoorVillage)e).getStatus())) {
				if (((PoorVillage)e).getStatus().equals("0")) {
					((PoorVillage)e).setStatus("取消出列");
				}
				if (((PoorVillage)e).getStatus().equals("1")) {
					((PoorVillage)e).setStatus("出列");
				}
			}
		}
			
			for(int i = 1; i <= excelResult.size(); i++) {
				HSSFSheet sheet = workbook.createSheet("贫困村记录表" + i);
				sheet.setDefaultColumnWidth(13);
				
				HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
				HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
				HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

				ExcelUtil.addTitle(sheet,cellName,"贫困村记录表",headStyle,columnTitleStyle);
				ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
			}
			return workbook;
		}
	
	
	/**
	 * 贫困村发展方向分类统计
	 */
	@Override
	@SuppressWarnings("all")
	public Pager poorVilageDevelopmentclass(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		//status = "未出列".equals(status) ? "0" : "已出列".equals(status) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageDevelopmentclass(pageNo,  pageSize,  year,  country,  village, status);
		
		return page;
	}

	 /**
	   * 贫困村地形地貌分类统计
	   */
	@Override
	public Pager poorVilageLandformclass(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		//type = "未出列".equals(type) ? "0" : "已出列".equals(type) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageLandformclass(pageNo,  pageSize,  year,  country,  village, status);
		
		return page;
	}

	/**
	 * 贫困村发展方向分类统计excel导出
	 */
	@Override
	public HSSFWorkbook exportDevelopmentclass(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String type) {
		String[] cellName = new String[] { "序号", "地区", "总贫困村数", "中心村镇村数","中心村镇占比","一般村镇村数","一般村镇占比","过渡村镇村数","过渡村镇占比"};
			String[] field = new String[] { "area","totalPoorVillage","zxcCount","zxcPercentage","ybcCount","ybcPercentage","gdcCount","gdcPercentage"};			
			
			List list  = new ArrayList<>();
			Map map = null;
			for(Map e : (List<Map>)poorVilageDevelopmentclass(user, 1, 5000, year,country , village, type).getResultList()) {
				map = new HashMap<>((Map)e.get("verbose"));
				map.put("area", e.get("area"));
				map.put("totalPoorVillage", e.get("totalPoorVillage").toString());
				list.add(map);
			}
			
			HSSFWorkbook workbook = new HSSFWorkbook();
			
			HSSFSheet sheet = workbook.createSheet("贫困村发展方向分类统计表");
			sheet.setDefaultColumnWidth(18);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

			ExcelUtil.addTitle(sheet,cellName,"贫困村发展方向分类统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
			
			return workbook;
	}

	/**
	 * 贫困村地形地貌分类统计excel导出
	 */
	@Override
	public HSSFWorkbook exportLandformclass(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String type) {
		String[] cellName = new String[] { "序号", "地区", "总贫困村数", "山区村镇村数","山区村镇占比","丘陵村镇村数","丘陵村镇占比","平坝村镇村数","平坝村镇占比"};
		String[] field = new String[] { "area","totalPoorVillage","sqCount","sqPercentage","qlCount","qlPercentage","pbCount","pbPercentage"};
		
		List list  = new ArrayList<>();
		Map map = null;
		for(Map e : (List<Map>)poorVilageLandformclass(user, 1, 5000, year,country , village, type).getResultList()) {
			map = new HashMap<>((Map)e.get("verbose"));
			map.put("area", e.get("area"));
			map.put("totalPoorVillage", e.get("totalPoorVillage").toString());
			list.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村地形地貌分类统计表");
		sheet.setDefaultColumnWidth(18);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村地形地貌分类统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		
		return workbook;
	}
	
	   /**
		 * 贫困村土地资源情况统计表
		 */
	@SuppressWarnings("all")
	public Pager poorVilageLand(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {
		
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 120 : pageSize;
		//type = "未出列".equals(type) ? "0" : "已出列".equals(type) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageLand(user,pageNo,  pageSize,  year,  country,  village, type);
		
		return page;
		
	}
	
	/**
	 *  贫困村土地资源情况统计表excel导出
	 */
	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportPoorVilageLand(McUser user,  Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {
		
			String[] cellName = new String[] { "序号", "地区", "总贫困村数", "总面积数", 
					"耕地面积.面积","耕地面积.占比", "有效灌溉面积.面积", "有效灌溉面积.占比", 
					"林地面积.面积", "林地面积.占比","退耕还林面积.面积", "退耕还林面积.占比", 
					"林果面积.面积" ,"林果面积.占比","牧草地面积.面积", "牧草地面积.占比",
					"水域面积.面积" ,"水域面积.占比"};
			String[] field = new String[] { "region", "totalPV", "totalA", 
					"cuedLandTotal","cuedLand", "irrigationTotal", "irrigation",
					"woodLandTotal","woodLand", "farmLandTotal", "farmLand", 
					"fruitTotal","fruit", "forageGrassTotal", "forageGrass", 
					"watersTotal" ,"waters"};
			
			List list = new ArrayList<>();
			Map map = null;

			for (Map e : (List<Map>) poorVilageLand(user, pageNo, pageSize, year, country, village, type).getResultList()) {
				map = new LinkedHashMap<>(30);
				if (e.get("verbose") != null) {
					map.putAll((Map) e.get("verbose"));
				}
				
				map.put("region", e.get("region"));//地区
		
				list.add(map);
			}

			HSSFWorkbook workbook = new HSSFWorkbook();

			HSSFSheet sheet = workbook.createSheet("贫困村土地资源情况统计表");
			sheet.setDefaultColumnWidth(19);

			HSSFCellStyle headStyle = ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle = ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle = ExcelUtil.getColumnTitleStyle(workbook);

			ExcelUtil.addTitle(sheet, cellName, "贫困村土地资源情况统计表", headStyle, columnTitleStyle);
			ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
			return workbook;
		}

	/**
	 * 贫困村村官和党员情况统计表
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Pager poorVilageOfficialsAndMembers(McUser user, Integer pageNo, Integer pageSize, String year,
			String country, String village, String type) {
	
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 120 : pageSize;
		//type = "未出列".equals(type) ? "0" : "已出列".equals(type) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageOfficialsAndMembers(user,pageNo,  pageSize,  year,  country,  village, type);
		
		return page;
		
	}

	/**
	 *  贫困村村官和党员情况统计表excel导出
	 */
	@Override
	public HSSFWorkbook exportPoorVilageOfficialsAndMembers(McUser user, Integer pageNo, Integer pageSize, String year,
			String country, String village, String type) {

		String[] cellName = new String[] { "序号", 
				"地区", "总贫困村数", "大学生村官数量","中共党员数量",
				"有村官的贫困村.村数", "有村官的贫困村.占比", 
				"有党员的贫困村.村数", "有党员的贫困村.占比"};
		String[] field = new String[] { 
				"region", "totalPV", "officer","party",
				"officials", "officialsP",
				"members","membersp"};
		
		List list = new ArrayList<>();
		Map map = null;

		for (Map e : (List<Map>) poorVilageOfficialsAndMembers(user, pageNo, pageSize, year, country, village, type).getResultList()) {
			map = new LinkedHashMap<>(30);
			if (e.get("verbose") != null) {
				map.putAll((Map) e.get("verbose"));
			}
			map.put("region", e.get("region"));//地区

			list.add(map);
		}

		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet("贫困村村官和党员情况统计表");
		sheet.setDefaultColumnWidth(19);

		HSSFCellStyle headStyle = ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle = ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle = ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet, cellName, "贫困村村官和党员情况统计表", headStyle, columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		return workbook;
	}


	@Override
	public Pager ruralTourism(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		//type = "未出列".equals(type) ? "0" : "已出列".equals(type) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.ruralTourism(user,pageNo,  pageSize,  year,  country,  village, status);
		
		return page;
	}

	@Override
	public Pager satelliteMonitor(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
	//	type = "未出列".equals(type) ? "0" : "已出列".equals(type) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.satelliteMonitor(user,pageNo,  pageSize,  year,  country,  village, status);
		
		return page;
	}

	 /**
	  * 贫困村开展乡村旅游情况统计表excel导出
	  */
	@Override
	public HSSFWorkbook exportruraiTourism(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String type) {
		String[] cellName = new String[] { "序号", "地区", "开展乡村旅游的户数", "开展乡村旅游的贫困村数","农家乐经营户数","乡村旅游从业人数","经营农家乐户数年均收入"};
		String[] field = new String[] { "region","tourismHouseholds","poorTourism","agritainment","ruralTourism","annualIncome"};
		
		List list  = new ArrayList<>();
		Map map = null;
		for(Map e : (List<Map>)ruralTourism(user, 1, 5000, year,country , village, type).getResultList()) {
			map = new LinkedHashMap<>(30);
			map.put("tourismHouseholds", (((Map) e).get("a"))!=null? ((Map) e).get("a") : "0");//开展乡村旅游的户数
			map.put("poorTourism", (((Map)e).get("b"))!=null? ((Map) e).get("b") : "0");//开展乡村旅游的贫困村数
			map.put("agritainment",(((Map) e).get("c"))!=null? ((Map) e).get("c") : "0");//农家乐经营户数
			map.put("ruralTourism", (((Map) e).get("d"))!=null? ((Map) e).get("d") : "0");//乡村旅游从业人数
			map.put("annualIncome", (((Map) e).get("e"))!=null? ((Map) e).get("e") : "0");//经营农家乐户数年均收入
			if(e.get("verbose")!=null){
				map.putAll((Map) e.get("verbose"));
			}
			map.put("region", e.get("region"));
			list.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村开展乡村旅游情况统计表");
		sheet.setDefaultColumnWidth(24);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村开展乡村旅游情况统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		
		return workbook;
	}

	/**
	 * 贫困村卫计情况统计表excel导出
	 */
	@Override
	public HSSFWorkbook exportsatelliteMonitor(McUser user, Integer pageNo, Integer pageSize, String year,String country, String village, String type) {
		String[] cellName = new String[] { "序号", "地区", "总贫困村数", "卫生室个数","公共卫生厕所个数","垃圾集中堆放点个数","无卫生室贫困村.村数","无卫生室贫困村.占比","无公共卫生厕所贫困村.村数","无公共卫生厕所贫困村.占比","无垃圾集中堆放点贫困村.村数","无公共卫生厕所贫困村.占比","执业（助理）医师数","每千人执业（助理）医师数"};
		String[] field = new String[] { "region","povertyVillages","healthRooms","toiletsPublic","numberGarbage","poorVillages","nopoorVillage","healthToilets","healthToiletsProportion","garbagecollection","garbagecollectionProportion","physicians","onethousandPhysicians"};
		
		List list  = new ArrayList<>();
		Map map = null;
		for(Map e : (List<Map>)satelliteMonitor(user, 1, 5000, year,country , village, type).getResultList()) {
			map = new LinkedHashMap<>(30);
			map.put("povertyVillages",(((Map) e).get("a"))!=null? ((Map) e).get("a") : "0");//总贫困村数
			map.put("healthRooms", (((Map) e).get("b"))!=null? ((Map) e).get("b") : "0");//卫生室个数
			map.put("toiletsPublic", (((Map) e).get("c"))!=null? ((Map) e).get("c") : "0");//公共卫生厕所个数
			map.put("numberGarbage",(((Map) e).get("d"))!=null? ((Map) e).get("d") : "0");//垃圾集中堆放点个数
			map.put("poorVillages", (((Map) e).get("e"))!=null? ((Map) e).get("e") : "0");//无卫生室贫困村.村数
			map.put("nopoorVillage", (((Map) e).get("f"))!=null? ((Map) e).get("f")+"%" : "0%");//无卫生室贫困村.占比
			map.put("healthToilets", (((Map) e).get("g"))!=null? ((Map) e).get("g") : "0");//无公共卫生厕所贫困村.村数
			map.put("healthToiletsProportion", (((Map) e).get("h"))!=null? ((Map) e).get("h")+"%" : "0%");//无公共卫生厕所贫困村.占比
			map.put("garbagecollection", (((Map) e).get("i"))!=null? ((Map) e).get("i") : "0");//无垃圾集中堆放点贫困村.村数
			map.put("garbagecollectionProportion",(((Map) e).get("j"))!=null? ((Map) e).get("j")+"%" : "0%");//无公共卫生厕所贫困村.占比
			map.put("physicians", (((Map) e).get("k"))!=null? ((Map) e).get("k") : "0");//执业（助理）医师数
			map.put("onethousandPhysicians", (((Map) e).get("l"))!=null? ((Map) e).get("l") : "0");//每千人执业（助理）医师数
			if(e.get("verbose")!=null){
				map.putAll((Map) e.get("verbose"));
			}
			map.put("region", e.get("region"));
			list.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村卫计情况统计表");
		sheet.setDefaultColumnWidth(24);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村卫计情况统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		
		return workbook;
	}

	/**
	 * 贫困村人口状况统计
	 */
	@Override
	public Pager pvcStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		status = "未出列".equals(status) ? "0" : "已出列".equals(status) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 5) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.pvcStatistics(user,pageNo,  pageSize,  year,  country,  village, status);
		
		return page;
	}

	@Override
	public Pager construction(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		//type = "未出列".equals(type) ? "0" : "已出列".equals(type) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.construction(user,pageNo,  pageSize,  year,  country,  village, status);
		
		return page;
	}

	/**
	 * 贫困村文化建设情况统计表excel导出
	 */
	@Override
	public HSSFWorkbook exportconstruction(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String type) {
		String[] cellName = new String[] { "序号", "地区", "总贫困村数", "文化（图书）室个数","村均文化（图书）室个数","有文化（图书）室贫困村.村数","有文化（图书）室贫困村.占比"};
		String[] field = new String[] { "region","povertyVillages","bookRooms","bookRoomsAverage","numberbookRooms","bookRoomsProportion"};
		
		List list  = new ArrayList<>();
		Map map = null;
		for(Map e : (List<Map>)construction(user, 1, 5000, year,country , village, type).getResultList()) {
			map = new LinkedHashMap<>(30);
			map.put("povertyVillages", (((Map) e).get("a"))!=null? ((Map) e).get("a") : "0");//总贫困村数
			map.put("bookRooms", (((Map) e).get("b"))!=null? ((Map) e).get("b") : "0");//文化（图书）室个数
			map.put("bookRoomsAverage", (((Map) e).get("c"))!=null? ((Map) e).get("c") : "0");//村均文化（图书）室个数
			map.put("numberbookRooms",(((Map) e).get("d"))!=null? ((Map) e).get("d") : "0");//有文化（图书）室贫困村.村数
			map.put("bookRoomsProportion", (((Map) e).get("e"))!=null? ((Map) e).get("e")+"%" : "0%");//有文化（图书）室贫困村.占比
			if(e.get("verbose")!=null){
				map.putAll((Map) e.get("verbose"));
			}
			map.put("region", e.get("region"));
			list.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村文化建设情况统计表");
		sheet.setDefaultColumnWidth(26);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村文化建设情况统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		
		return workbook;
	}

	@Override
	public Pager information(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		//type = "未出列".equals(type) ? "0" : "已出列".equals(type) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.information(user,pageNo,  pageSize,  year,  country,  village, status);
		
		return page;
	}

	@Override
	public Pager duplicateIDnumber(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.duplicateIDnumber(user,pageNo,  pageSize,  year,  country,  village);
		
		return page;
	}

	/**
	  * 贫困村信息化建设情况统计表excel导出
	  */
	@Override
	public HSSFWorkbook exportinformation(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String type) {
		String[] cellName = new String[] { "序号", "地区", "总贫困村数","总户数","通宽带户数.户数","通宽带户数.占比","通宽带的村小学个数","能用手机上网的户数","信息员数"};
		String[] field = new String[] { "region","povertyVillages","households","tkdhouseholds","tkdhouseholdszb","tkdschool","phonehouseholds","numberInformation"};
		
		List list  = new ArrayList<>();
		Map map = null;
		for(Map e : (List<Map>)information(user, 1, 5000, year,country , village, type).getResultList()) {
			map = new LinkedHashMap<>(30);
			map.put("povertyVillages", (((Map) e).get("a"))!=null? ((Map) e).get("a") : "0");//总贫困村数
			map.put("households", (((Map) e).get("b"))!=null? ((Map) e).get("b") : "0");//总户数
			map.put("tkdhouseholds", (((Map) e).get("c"))!=null? ((Map) e).get("c") : "0");//通宽带户数.户数
			map.put("tkdhouseholdszb",(((Map) e).get("d"))!=null? ((Map) e).get("d")+"%" : "0%");//通宽带户数.占比
			map.put("tkdschool", (((Map) e).get("e"))!=null? ((Map) e).get("e") : "0");//通宽带的村小学个数
			map.put("phonehouseholds", (((Map) e).get("f"))!=null? ((Map) e).get("f") : "0");//能用手机上网的户数
			map.put("numberInformation", (((Map) e).get("g"))!=null? ((Map) e).get("g") : "0");//信息员数
			if(e.get("verbose")!=null){
				map.putAll((Map) e.get("verbose"));
			}
			map.put("region", e.get("region"));
			list.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村信息化建设情况统计表");
		sheet.setDefaultColumnWidth(26);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村信息化建设情况统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		
		return workbook;
	}

	/**
	 * 证件号码重复统计表excel导出
	 */
	@Override
	@Deprecated
	public HSSFWorkbook duplicateId(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village) {
		String[] cellName = new String[] { "序号", "地区", "证件号码重复数量"};
		String[] field = new String[] { "region","povertyVillages"};
		
		List list  = new ArrayList<>();
		Map map = null;
		for(Map e : (List<Map>)duplicateIDnumber(user, 1, 5000, year,country , village).getResultList()) {
			map = new HashMap<>(e);
			list.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("证件号码重复统计表");
		sheet.setDefaultColumnWidth(26);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"证件号码重复统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		
		return workbook;
	}
	
	/**
   	 * 贫困村道路硬化及通班车情况统计表
   	 */
	public Pager poorVilageRoad(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {

		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 120 : pageSize;
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageRoad(user,pageNo,  pageSize,  year,  country,  village, type);
		
		return page;
	}

	   /**
   	 *  贫困村道路硬化及通班车情况统计表excel导出
   	 */
	public HSSFWorkbook exportPoorVilageRoad(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {
		
			String[] cellName = new String[] { "序号", 
					"地区", "总贫困村数", 
					"到乡镇未通沥青（水泥）路.村数", "到乡镇是否通沥青（水泥）路.占比", 
					"未通客运班车数.村数", "未通客运班车数.占比"};
			String[] field = new String[] { 
					"region", "totalPV", 
					"road", "roadP",
					"bus","busP"};
			
			List list = new ArrayList<>();
			Map map = null;

			for (Map e : (List<Map>) poorVilageRoad(user, pageNo, pageSize, year, country, village, type).getResultList()) {
				map = new LinkedHashMap<>(30);
				if (e.get("verbose") != null) {
					map.putAll((Map) e.get("verbose"));
				}
				
				map.put("region", e.get("region"));//地区
				
				list.add(map);
			}

			HSSFWorkbook workbook = new HSSFWorkbook();

			HSSFSheet sheet = workbook.createSheet("贫困村道路硬化及通班车情况统计表");
			sheet.setDefaultColumnWidth(19);

			HSSFCellStyle headStyle = ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle = ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle = ExcelUtil.getColumnTitleStyle(workbook);

			ExcelUtil.addTitle(sheet, cellName, "贫困村道路硬化及通班车情况统计表", headStyle, columnTitleStyle);
			ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
			return workbook;
		}

	/**
 	 *  贫困村饮水情况统计表
 	 */
	@Override
	public Pager poorVilageWater(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 120 : pageSize;
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageWater(user,pageNo,  pageSize,  year,  country,  village, type);
		
		return page;
	}
	

	 /**
 	 *  贫困村饮水情况统计表excel导出
 	 */
	@Override
	public HSSFWorkbook exportPoorVilageWater(McUser user, Integer pageNo, Integer pageSize, String year,
			String country, String village, String type) {

		String[] cellName = new String[] { "序号", 
				"地区", "总户数", 
				"未实现饮水安全户数.户数", "未实现饮水安全户数.占比", 
				"饮水困难户数.户数", "饮水困难户数.占比"};
		String[] field = new String[] { 
				"region", "total", 
				"security", "securityP",
				"difficulty","difficultyP"};
		
		List list = new ArrayList<>();
		Map map = null;

		for (Map e : (List<Map>) poorVilageWater(user, pageNo, pageSize, year, country, village, type).getResultList()) {
			map = new LinkedHashMap<>(30);
			if (e.get("verbose") != null) {
				map.putAll((Map) e.get("verbose"));
			}
			
			map.put("region", e.get("region"));//地区
			
			list.add(map);
		}

		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet("贫困村饮水情况统计表");
		sheet.setDefaultColumnWidth(19);

		HSSFCellStyle headStyle = ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle = ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle = ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet, cellName, "贫困村饮水情况统计表", headStyle, columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		return workbook;
	}

	
	/**
   	 * 贫困村危房改造情况统计表
   	 */
	@Override
	public Pager poorVilageDanger(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 120 : pageSize;
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageDanger(user,pageNo,  pageSize,  year,  country,  village, type);
		
		return page;
		
	}

	
	/**
 	 *  贫困村危房改造情况统计表excel导出
 	 */
	@Override
	public HSSFWorkbook exportPoorVilageDanger(McUser user, Integer pageNo, Integer pageSize, String year,
			String country, String village, String type) {
	
			String[] cellName = new String[] { "序号", 
					"地区", "总户数", 
					"危房户数.户数", "危房户数.占比"};
			String[] field = new String[] { 
					"region", "total", 
					"danger","dangerP"};
			
			List list = new ArrayList<>();
			Map map = null;

			for (Map e : (List<Map>) poorVilageDanger(user, pageNo, pageSize, year, country, village, type).getResultList()) {
				map = new LinkedHashMap<>(30);
				if (e.get("verbose") != null) {
					map.putAll((Map) e.get("verbose"));
				}
				
				map.put("region", e.get("region"));//地区
			
				list.add(map);
			}

			HSSFWorkbook workbook = new HSSFWorkbook();

			HSSFSheet sheet = workbook.createSheet("贫困村危房改造情况统计表");
			sheet.setDefaultColumnWidth(19);

			HSSFCellStyle headStyle = ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle = ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle = ExcelUtil.getColumnTitleStyle(workbook);

			ExcelUtil.addTitle(sheet, cellName, "贫困村危房改造情况统计表", headStyle, columnTitleStyle);
			ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
			return workbook;
		}

	
	/**
 	 *  贫困村参加社会保险分组情况统计表
 	 */
	@Override
	public Pager poorVilageInsurance(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {
		
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 120 : pageSize;
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageInsurance(user,pageNo,  pageSize,  year,  country,  village, type);
		
		return page;
		}

	
	/**
 	 *  贫困村参加社会保险分组情况统计表excel导出
 	 */
	@Override
	public HSSFWorkbook exportPoorVilageInsurance(McUser user, Integer pageNo, Integer pageSize, String year,
			String country, String village, String type) {
		
				String[] cellName = new String[] { "序号", 
						"地区", "总户数", 
						"参加新型农村合作医疗人数.人数", "参加新型农村合作医疗人数.占比", 
						"参加城镇职工基本养老保险人数.人数", "参加城镇职工基本养老保险人数.占比", 
						"参加城乡居民基本养老保险人数.人数", "参加城乡居民基本养老保险人数.占比"};
				String[] field = new String[] { 
						"region", "total", 
						"medical", "medicalP", 
						"townPension", "townPensionP", 
						"ruralPension","ruralPensionP"};
				
				List list = new ArrayList<>();
				Map map = null;

				for (Map e : (List<Map>) poorVilageInsurance(user, pageNo, pageSize, year, country, village, type).getResultList()) {
					map = new LinkedHashMap<>(30);
					if (e.get("verbose") != null) {
						map.putAll((Map) e.get("verbose"));
					}
					
					map.put("region", e.get("region"));//地区
					
					list.add(map);
				}

				HSSFWorkbook workbook = new HSSFWorkbook();

				HSSFSheet sheet = workbook.createSheet("贫困村参加社会保险分组情况统计表");
				sheet.setDefaultColumnWidth(19);

				HSSFCellStyle headStyle = ExcelUtil.getTitleStyle(workbook);
				HSSFCellStyle cellStyle = ExcelUtil.getCellStyle(workbook);
				HSSFCellStyle columnTitleStyle = ExcelUtil.getColumnTitleStyle(workbook);

				ExcelUtil.addTitle(sheet, cellName, "贫困村参加社会保险分组情况统计表", headStyle, columnTitleStyle);
				ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
				return workbook;
			}

	
	/**
 	 *  贫困村特色产业增收情况统计表
 	 */
	@Override
	public Pager poorVilageCharacteristic(McUser user, Integer pageNo, Integer pageSize, String year, String country,
			String village, String type) {
	
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 120 : pageSize;
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poorVilageCharacteristic(user,pageNo,  pageSize,  year,  country,  village, type);
		
		return page;
		}

	
	/**
 	 *  贫困村特色产业增收情况统计表excel导出
 	 */
	@Override
	public HSSFWorkbook exportPoorVilageCharacteristic(McUser user, Integer pageNo, Integer pageSize, String year,
			String country, String village, String type) {
	
					String[] cellName = new String[] { "序号", 
							"地区", "总贫困村数", "总户数", "农民专业合作社个数", 
							"无农民专业合作社贫困村.村数", "无农民专业合作社贫困村.占比", 
							"有1个农民专业合作社贫困村.村数", "有1个农民专业合作社贫困村.占比", 
							"有2个-5个农民专业合作社贫困村.村数", "有2个-5个农民专业合作社贫困村.占比", 
							"有5个以上农民专业合作社贫困村.村数", "有5个以上农民专业合作社贫困村.占比", 
							"参加农民专业合作社贫困户.户数", "参加农民专业合作社贫困户.占比"};
					String[] field = new String[] { 
							"region", "totalPV", "total", "cooperative", 
							"zero", "zeroP", 
							"one", "oneP", 
							"two", "twoP", 
							"five", "fiveP", 
							"major","majorP"};
					
					List list = new ArrayList<>();
					Map map = null;

					for (Map e : (List<Map>) poorVilageCharacteristic(user, pageNo, pageSize, year, country, village, type).getResultList()) {
						map = new LinkedHashMap<>(30);
						if (e.get("verbose") != null) {
							map.putAll((Map) e.get("verbose"));
						}
						
						map.put("region", e.get("region"));//地区
//						map.put("totalPV", e.get("totalPV"));//总贫困村数
//						map.put("total", e.get("total"));//总户数
//						map.put("cooperative", e.get("cooperative"));//农民专业合作社个数
//						map.put("zero", e.get("zero"));//无农民专业合作社贫困村.村数
//						map.put("zeroP", e.get("zeroP"));//无农民专业合作社贫困村.占比
//						map.put("one", e.get("one"));//有1个农民专业合作社贫困村.村数
//						map.put("oneP", e.get("oneP"));//有1个农民专业合作社贫困村.占比
//						map.put("two", e.get("two"));//有2个-5个农民专业合作社贫困村.村数
//						map.put("twoP", e.get("twoP"));//有2个-5个农民专业合作社贫困村.占比
//						map.put("five", e.get("five"));//有5个以上农民专业合作社贫困村.村数
//						map.put("fiveP", e.get("fiveP"));//有5个以上农民专业合作社贫困村.占比
//						map.put("major", e.get("major"));//参加农民专业合作社贫困户.户数
//						map.put("majorP", e.get("majorP"));//参加农民专业合作社贫困户.占比
						list.add(map);
					}

					HSSFWorkbook workbook = new HSSFWorkbook();

					HSSFSheet sheet = workbook.createSheet("贫困村特色产业增收情况统计表");
					sheet.setDefaultColumnWidth(19);

					HSSFCellStyle headStyle = ExcelUtil.getTitleStyle(workbook);
					HSSFCellStyle cellStyle = ExcelUtil.getCellStyle(workbook);
					HSSFCellStyle columnTitleStyle = ExcelUtil.getColumnTitleStyle(workbook);

					ExcelUtil.addTitle(sheet, cellName, "贫困村特色产业增收情况统计表", headStyle, columnTitleStyle);
					ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
					return workbook;
				}

	/**
	 * 贫困村人口状况统计excel导出
	 */
	@Override
	public HSSFWorkbook exportPvcStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country,
		String village, String status) {
		String[] cellName = new String[] { "序号", "地区", "总贫困村数", "总户数", "总人数", "贫困户数", "贫困人口数", 
			"贫困村贫困发生率","五保户数", "五保人口数", "低保户数", "低保人口数", "少数民族人口数", "妇女人口数", "残疾人口数" , "劳动力人数", "外出务工人数"};
		String[] field = new String[] { "area", "totalPoorVillage", "totalFamily", "totalPeople", "totalPoorFamily", "totalPoorPeople",
			"probability", "fiveHouse", "fiveNumber", "lowHouse", "lowNumber", "lessNumber", "womanNumber", "remnantNumber", "labourNumber", "gooutNumber" };
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村人口状况统计表");
		sheet.setDefaultColumnWidth(18);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村人口状况统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, poorViDao.pvcStatistics(user, 1,5000, year,country,village, status).getResultList(), field, cellStyle, true);
		
		return workbook;
	}
	
	
	@Override
	public Pager poordecreaseofPopulation(McUser user, Integer pageNo, Integer pageSize, String year, String country,String village, String type) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		type = "未出列".equals(type) ? "0" : "已出列".equals(type) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 4) {
			return new Pager(pageNo,pageSize,0,null);
		}

		//镇级权限只能获取当前镇
		if(user.getUserLevel().getLevel() == 4) {
			Area area =  user.getArea();
			if(area == null || area.getAreaCode().length() < 9) {
				return new Pager(pageNo,pageSize,0,null);
			}
			country = area.getAreaCode();
		}
		
		Pager page = poorViDao.poordecreaseofPopulation(user,pageNo,  pageSize,  year,  country,  village, type);
		
		return page;
	}

	/**
	 * 贫困人口净减少统计表excel导出
	 */
	@Override
	public HSSFWorkbook exportpoordecreaseofPopulation(McUser user, Integer pageNo, Integer pageSize, String year,String country, String village, String type) {
		String[] cellName = new String[] { "序号","地区","上年贫困人数", "当年贫困人数","净减少人数","上年贫困户数","当年贫困户数","净减少户数"};
		String[] field = new String[] { "region","lastnumber","thatnumber","reduce","lasthouseholds","thathouseholds","reducehouseholds"};
		
		List list  = new ArrayList<>();
		Map map = null;
		for(Map e : (List<Map>)poordecreaseofPopulation(user, 1, 5000, year,country , village, type).getResultList()) {
			map = new LinkedHashMap<>(30);
			map.put("lastnumber", (((Map) e).get("a"))!=null? ((Map) e).get("a") : "0");//上年贫困人数
			map.put("thatnumber", (((Map) e).get("b"))!=null? ((Map) e).get("b") : "0");//当年贫困人数
			map.put("reduce",(((Map) e).get("c"))!=null? ((Map) e).get("c") : "0");//净减少人数
			map.put("lasthouseholds",(((Map) e).get("d"))!=null? ((Map) e).get("d") : "0");//上年贫困户数
			map.put("thathouseholds",(((Map) e).get("e"))!=null? ((Map) e).get("e") : "0");//当年贫困户数
			map.put("reducehouseholds",(((Map) e).get("f"))!=null? ((Map) e).get("f") : "0");//净减少户数
			if(e.get("verbose")!=null){
				map.putAll((Map) e.get("verbose"));
			}
			map.put("region", e.get("region"));
			list.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困人口净减少统计表");
		sheet.setDefaultColumnWidth(26);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困人口净减少统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		
		return workbook;
	}

	/**
	 * 贫困村年人均收入分组情况统计
	 */
	@Override
	public Pager perIncomeStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String status) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		status = "未出列".equals(status) ? "0" : "已出列".equals(status) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < LevelUtil.ALL || user.getUserLevel().getLevel() > LevelUtil.TOWN) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}
		Pager page = poorViDao.perIncomeStatistics(user, pageNo, pageSize, year, country, village, status);
		
		return page;
	}
	
	/**
	 * 贫困村年人均收入分组情况统计excel导出
	 */
	@Override
	public HSSFWorkbook exportPerIncomeStatistics(McUser user, Object object, Object object2, String year, String country,
		String village, String status) {
		String[] cellName = new String[] { "序号", "地区", "总贫困村数", "1000元（不含）以下\r\n村数", "1000元（不含）以下\r\n占比", 
			"1000元-2000元（不含）\r\n村数", "1000元-2000元（不含）\r\n占比", 
			"2000元-3000元（不含）\r\n村数","2000元-3000元（不含）\r\n占比",
			"3000元-4000元（不含）\r\n村数", "3000元-4000元（不含）\r\n占比", 
			"4000元-5000元（不含）\r\n村数", "4000元-5000元（不含）\r\n占比", 
			"5000元（含）以上\r\n村数", "5000元（含）以上\r\n占比" };
		String[] field = new String[] { "area", "totalPoorVillage", "c1", "percentC1", "c2", "percentC2",
			"c3", "percentC3", "c4", "percentC4", "c5", "percentC5", "c", "percentC"};
		Map temp = new HashMap<>();
		List result = new ArrayList<>();
		for(Map map : (List<Map>)perIncomeStatistics(user, 1,5000, year,country,village, status).getResultList()) {
			temp = new HashMap<>();
			temp.put("area", map.get("area"));
			temp.put("totalPoorVillage", map.get("countVillage") == null ? 0 : map.get("countVillage"));
			temp.put("c1", map.get("c1")==null ? 0 : map.get("c1"));
			temp.put("percentC1", map.get("percentC1")==null ? 0 : map.get("percentC1"));
			temp.put("c2", map.get("c2")==null ? 0 : map.get("c2"));
			temp.put("percentC2", map.get("percentC2")==null ? 0 : map.get("percentC2"));
			temp.put("c3", map.get("c3")==null ? 0 : map.get("c3"));
			temp.put("percentC3", map.get("percentC3")==null ? 0 : map.get("percentC3"));
			temp.put("c4", map.get("c4")==null ? 0 : map.get("c4"));
			temp.put("percentC4", map.get("percentC4")==null ? 0 : map.get("percentC4"));
			temp.put("c5", map.get("c5")==null ? 0 : map.get("c5"));
			temp.put("percentC5", map.get("percentC5")==null ? 0 : map.get("percentC5"));
			temp.put("c", map.get("c")==null ? 0 : map.get("c"));
			temp.put("percentC", map.get("percentC")==null ? 0 : map.get("percentC"));
			result.add(temp);
		}
		
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村年人均收入分组情况统计表");
		sheet.setDefaultColumnWidth(22);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
	   	HSSFCellStyle columnTitleStyle =  ExcelUtil.getWrapColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村年人均收入分组情况统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet,result, field, cellStyle, true);
		
		return workbook;
	}

	/**
	 * 贫困村村集体经济收入分组情况统计
	 */
	@Override
	public Pager villageIncomeStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String status) {
		
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		status = "未出列".equals(status) ? "0" : "已出列".equals(status) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < LevelUtil.ALL || user.getUserLevel().getLevel() > LevelUtil.TOWN) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}
		Pager page = poorViDao.villageIncomeStatistics(user, pageNo, pageSize, year, country, village, status);
		
		return page;
	}

	/**
	 * 贫困村村集体经济收入分组情况统计excel导出
	 */
	@Override
	public HSSFWorkbook exportVillageIncomeStatistics(McUser user, Object object, Object object2, String year, String country,
		String village, String status) {
		
		String[] cellName = new String[] { "序号", "地区", "总贫困村数","村均集体收入\r\n（万元）", "无集体收入贫困\r\n村数", "0-50万元村数", 
			"0-50万元占比", "50-100万元村数", "50-100万元占比","100-200万元村数","100-200万元占比", "200-300万元村数", 
			"200-300万元占比", "300万元以上村数", "300万元以上占比"};
		String[] field = new String[] { "area", "totalPoorVillage", "collectiveAVG", "c", "c1","percentC1","c2", "percentC2",
			"c3", "percentC3", "c4", "percentC4", "c5", "percentC5"};
		Map temp = new HashMap<>();
		List result = new ArrayList<>();
		for(Map map : (List<Map>)villageIncomeStatistics(user, 1,5000, year,country,village, status).getResultList()) {
			temp = new HashMap<>();
			temp.put("area", map.get("area"));
			temp.put("totalPoorVillage", map.get("countVillage") == null ? 0 : map.get("countVillage"));
			temp.put("collectiveAVG", map.get("collectiveAVG") == null ? 0 : map.get("collectiveAVG"));
			temp.put("c1", map.get("c1")==null ? 0 : map.get("c1"));
			temp.put("percentC1", map.get("percentC1")==null ? 0 : map.get("percentC1"));
			temp.put("c2", map.get("c2")==null ? 0 : map.get("c2"));
			temp.put("percentC2", map.get("percentC2")==null ? 0 : map.get("percentC2"));
			temp.put("c3", map.get("c3")==null ? 0 : map.get("c3"));
			temp.put("percentC3", map.get("percentC3")==null ? 0 : map.get("percentC3"));
			temp.put("c4", map.get("c4")==null ? 0 : map.get("c4"));
			temp.put("percentC4", map.get("percentC4")==null ? 0 : map.get("percentC4"));
			temp.put("c5", map.get("c5")==null ? 0 : map.get("c5"));
			temp.put("percentC5", map.get("percentC5")==null ? 0 : map.get("percentC5"));
			temp.put("c", map.get("c")==null ? 0 : map.get("c"));
			result.add(temp);
		}
		
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村村集体经济收入分组情况统计表");
		sheet.setDefaultColumnWidth(20);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
	   	HSSFCellStyle columnTitleStyle =  ExcelUtil.getWrapColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村村集体经济收入分组情况统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet,result, field, cellStyle, true);
		
		return workbook;
	}

	/**
	 * 贫困村未脱贫人口情况统计
	 */
	@Override
	public Pager wtpStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String status) {
		
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;
		status = "未出列".equals(status) ? "0" : "已出列".equals(status) ? "1" : "";
		// 权限错误,镇以下没有权限
		if (user.getUserLevel().getLevel() < LevelUtil.ALL || user.getUserLevel().getLevel() > LevelUtil.TOWN) {
			return new Pager(pageNo,pageSize,0,new ArrayList<>(0));
		}
		Pager page = poorViDao.wtpStatistics(user, pageNo, pageSize, year, country, village, status);
		
		return page;
	}

	/**
	 * 贫困村未脱贫人口情况统计excel导出
	 */
	@Override
	public HSSFWorkbook exportWtpStatistics(McUser user, Object object, Object object2, String year, String country, String village,
		String status) {
		
		String[] cellName = new String[] { "序号", "地区", "总户数","总人数", "贫困村户数", "贫困村户数占比", 
			"贫困村人数", "贫困村人数占比", "非贫困村户数","非贫困村户数占比","非贫困村人数", "非贫困村人数占比"};
		String[] field = new String[] { "area", "countF", "countP", "countPF", "percentPF","countPP","percentPP",
			"countUPF", "percentUPF", "countUPP", "percentUPP"};
		Map temp = new HashMap<>();
		List result = new ArrayList<>();
		for(Map map : (List<Map>)wtpStatistics(user, 1,5000, year,country,village, status).getResultList()) {
			temp = new HashMap<>();
			temp.put("area", map.get("area"));
			temp.put("countF", map.get("countF") == null ? 0 : map.get("countF"));
			temp.put("countP", map.get("countP") == null ? 0 : map.get("countP"));
			temp.put("countPF", map.get("countPF")==null ? 0 : map.get("countPF"));
			temp.put("percentPF", map.get("percentPF")==null ? 0 : map.get("percentPF"));
			temp.put("countPP", map.get("countPP")==null ? 0 : map.get("countPP"));
			temp.put("percentPP", map.get("percentPP")==null ? 0 : map.get("percentPP"));
			temp.put("countUPF", map.get("countUPF")==null ? 0 : map.get("countUPF"));
			temp.put("percentUPF", map.get("percentUPF")==null ? 0 : map.get("percentUPF"));
			temp.put("countUPP", map.get("countUPP")==null ? 0 : map.get("countUPP"));
			temp.put("percentUPP", map.get("percentUPP")==null ? 0 : map.get("percentUPP"));
			result.add(temp);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet("贫困村未脱贫人口情况统计表");
		sheet.setDefaultColumnWidth(20);
		
		HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
	   	HSSFCellStyle columnTitleStyle =  ExcelUtil.getWrapColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet,cellName,"贫困村未脱贫人口情况统计表",headStyle,columnTitleStyle);
		ExcelUtil.addContextByList(sheet,result, field, cellStyle, true);
		
		return workbook;
	}

	@Override
	public List<PoorVillage> getPoorVillages(Long id) {
		List<PoorVillage> list = sessionFactory.getCurrentSession().createSQLQuery(
		"SELECT * FROM poor_village pv WHERE id IN(SELECT hp.PoorVillageId FROM helpunits_and_poorvillage hp WHERE hp.helpUnitsId = "+id+")").addEntity(PoorVillage.class).list();
		return list;
	}

	@Override
	public boolean isExsitName(Long id,String name,String annual) {
	
		Map condition = new HashMap<>();
		condition.put("name", name);
		condition.put("id", id);
		condition.put("annual", annual);
	    List list = poorViDao.queryList(" from PoorVillage as pv where isDel='0' and annual= :annual and name= :name and id != :id", condition);
	    return list != null && !list.isEmpty();
	}
	
	
	@Override
	public PoorVillage isName(Long id) {
		// TODO Auto-generated method stub
		return get(id);
	}

	@Override
	public Long addPoorVillageHistory(PoorVillageHistory poorVillageHistory) {
		return poorViDao.addPoorVillageHistory(poorVillageHistory);
	}

	@Override
	public PoorVillageHistory getPoorVillageHistory(Long id) {
		
		return poorViDao.getPoorVillageHistory(id);
	}

	@Override
	public boolean isExsitPoorVillage(PoorVillage poorVillage) {
		Map condition = new HashMap<>();
		condition.put("cry", poorVillage.getCry());
		condition.put("name", poorVillage.getName());
		condition.put("id", poorVillage.getId());
		condition.put("annual", poorVillage.getAnnual());
		condition.put("type", "0");
	    List list = poorViDao.queryList(" from PoorVillage as pv where isDel=0 and annual= :annual and type= :type and cry = :cry and name= :name and id != :id", condition);
	    return list != null && !list.isEmpty();
	}

    @Override
    public void updatePoorFamilyCount(String annual, Long areaID, Long poorFamilyCount) {
	    poorViDao.modifyByHql("update PoorVillage set poor='"+poorFamilyCount+"' where isDel='0' and annual='"+annual+"' and area.id="+areaID);
    }

    @Override
    public void updateFamilyCount(String annual, Long areaID, Long familyCount) {
        poorViDao.modifyByHql("update PoorVillage set poorNumber='"+familyCount+"' where isDel='0' and annual='"+annual+"' and area.id="+areaID);
    }

}

